package com.app.OOS.service;



import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;


import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.app.OOS.domain.Score;
import com.app.OOS.mapper.UploadMapper;



@Service
public class UploadService {

	Logger log = LogManager.getLogger(UploadService.class);
	@Autowired
	UploadMapper uploadMapper;
	
	
	@SuppressWarnings("resource")
	public String readExcel(MultipartFile file) throws Exception{
		
		Workbook wb;
		
		InputStream is;
		String rowMessage = "";
		List<Score> list = new ArrayList<Score>();
		String fileName = file.getOriginalFilename();
		
		boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")){
            isExcel2003 = false;
        }
		
		is = file.getInputStream();
		
		if (isExcel2003) {
			wb = new HSSFWorkbook(is);
		}else {
			wb = new XSSFWorkbook(is);
		}
		try {
			
			Sheet sheet = wb.getSheetAt(0);
			//得到总行数
			int totalRows = sheet.getPhysicalNumberOfRows();//getPhysicalNumberOfRows()是获取不为空的行个数
			//总列数
			int totalCells = 0;
			//得到Excel的列数(前提是有行数),从第二行算起
			if(totalRows >=2 && sheet.getRow(1) != null) {
				totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
			}
			
			log.debug("总行数:" + totalRows + "总列数:" + totalCells);
			
			
			// 循环Excel行数,从第二行开始。标题不入库
			for(int r = 1; r < totalRows; r++) {
				
				Row row = sheet.getRow(r);
				if(row == null || 0 == row.getPhysicalNumberOfCells()) {
					rowMessage ="数据表格没有数据";
					return rowMessage;
				}
				
				Score s = new Score();
				
				int userId; //学生学号
				String userName;
				int score;
			
				
				
				
				
				// 循环Excel的列
				// 循环Excel的列
				for (int c=0;c<totalCells;c++) {
					Cell cell = row.getCell(c);
					if(cell != null) {
						if(c == 0) {
//							String Id = cell.getStringCellValue();
							userId = (int)Math.round(cell.getNumericCellValue());
							
								s.setUserId(userId);
							
						}else if (c == 1) {
							cell.setCellType(CellType.STRING);
							userName = cell.getStringCellValue();
							
								s.setUserName(userName);
							
						}else if (c==2) {

						
							score = (int)Math.round(cell.getNumericCellValue());
							
							
								s.setScore(score);
								
								
							}
							
						}
					}
				
			
				
				
				
				
					list.add(s);
					
				}
				
				
		
			
			uploadMapper.insertScore(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					is = null;
					e.printStackTrace();
				}
			}
		}
		
		rowMessage="导入成功";
		return rowMessage;
			
			
		
	}
}

